﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace EasyLibrary.AR.Classes
{
    internal class BDconn
    {
        private readonly String _connectionString = ConnectionString.GetUsingHostname();

        public int executeCommandNoQuery(string commandQuery)
        {
            using (var db = new SqlConnection(_connectionString))
            using (var sp = new SqlCommand(commandQuery, db) {CommandType = CommandType.Text})
            {
                db.Open();
                int erro = sp.ExecuteNonQuery();
                db.Close();
                return erro;
            }
        }

        public int executeCommandScalar(string commandQuery)
        {
            using (var db = new SqlConnection(_connectionString))
            using (var sp = new SqlCommand(commandQuery, db) {CommandType = CommandType.Text})
            {
                db.Open();
                int firstC = Convert.ToInt32(sp.ExecuteScalar());
                db.Close();
                return firstC;
            }
        }

        public DataSet executeCommandOnDataSet(string query)
        {
            using (var db = new SqlConnection(_connectionString))
            using (var sp = new SqlCommand(query, db) {CommandType = CommandType.Text, CommandTimeout = 3200})
            {
                db.Open();
                var dataAdapter = new SqlDataAdapter(sp);
                var resultDataSet = new DataSet {Locale = CultureInfo.InvariantCulture};
                dataAdapter.Fill(resultDataSet);
                db.Close();
                return resultDataSet;
            }
        }

        /*
        public void executedropdownlistdatabind(object ddl, string table, string where, string orderBy, string textField, string valueField)
        {
            var dd = ddl as DropDownList;
            if (dd == null) return;

            var sqlQuery = "SELECT " + textField + "," + valueField + " From " + table;
            if (where != "")
                sqlQuery += " WHERE " + where + " ";
            if (orderBy != "")
                sqlQuery += " ORDER BY " + orderBy;

            var myDataSet = executeCommandOnDataSet(sqlQuery);

            dd.DataSource = myDataSet;
            dd.DataTextField = textField;
            dd.DataValueField = valueField;
            dd.DataBind();
        }

        public void executedveiculos(object ddl, int util)
        {
            var dd = ddl as DropDownList;
            if (dd == null) return;


            var myDataSet = executeCommandOnDataSet("SELECT DISTINCT Viaturas.matricula, Viaturas.IDViaturas  FROM Viaturas INNER JOIN Kits ON Viaturas.RefIDKits = Kits.IDkits INNER JOIN KitStartLogs ON Kits.IDkits = KitStartLogs.RefIDKits INNER JOIN GeoCodes ON KitStartLogs.IDKitStartLogs = GeoCodes.RefIDKitStartLogs INNER JOIN ViaturasUtilizadores ON ViaturasUtilizadores.RefIDViaturas = Viaturas.IDViaturas WHERE ViaturasUtilizadores.RefIDUtilizadores = " + util + " ORDER BY Viaturas.matricula");

            dd.DataSource = myDataSet;
            dd.DataTextField = "Matricula";
            dd.DataValueField = "IDViaturas";
            dd.DataBind();
        }
        public void executedveiculos2(object ddl, int util)
        {
            var dd = ddl as DropDownList;
            if (dd == null) return;


            var myDataSet = executeCommandOnDataSet("SELECT DISTINCT Viaturas.matricula, Viaturas.IDViaturas FROM Viaturas INNER JOIN ViaturasUtilizadores ON ViaturasUtilizadores.RefIDViaturas = Viaturas.IDViaturas WHERE ViaturasUtilizadores.RefIDUtilizadores = " + util + " AND Viaturas.Activo = 1 ORDER BY Viaturas.matricula");

            dd.DataSource = myDataSet;
            dd.DataTextField = "Matricula";
            dd.DataValueField = "IDViaturas";
            dd.DataBind();
        } */
    }
}